package com.neuedu.jdbc.dao;

import com.neuedu.jdbc.po.Dept;
import com.neuedu.jdbc.util.DBUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author 金山
 * 项目：jdbc
 * site: https://blog.fulfill.com.cn
 * 描述
 * @data 2024/11/812:23
 */
public class DeptDao {

    public List<Dept> selectAll(){
        //
        Connection connection =  null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        List<Dept> list = new ArrayList();
        try {
            connection = DBUtils.getConnection();


            String sql = "select deptno,dname,loc from dept ";
            stmt = connection.prepareStatement(sql);


            //可以绑定变量，没有？  可以不绑定

            //执行sql
            rs = stmt.executeQuery();
            while (rs.next()){
                int deptno = rs.getInt("deptno");
                String dname = rs.getString("dname");
                String loc = rs.getString("loc");


                //一行数据
                Dept dept = new Dept();
                dept.setDeptno(deptno);
                dept.setDname(dname);
                dept.setLoc(loc);

                //将行 数据 放到 list集合中
                list.add(dept);
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        return  list;
    }

    public Map selectByIdMap(int id){
        //
        Connection connection =  null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        Map row = null;
        try {
            connection = DBUtils.getConnection();


            String sql = "select deptno,dname,loc from dept where deptno = ?  ";
            stmt = connection.prepareStatement(sql);


            //可以绑定变量，没有？  可以不绑定
            stmt.setInt(1,id);

            //执行sql
            rs = stmt.executeQuery();
            if (rs.next()){
                int deptno = rs.getInt("deptno");
                String dname = rs.getString("dname");
                String loc = rs.getString("loc");

                //一行数据
                row = new HashMap();
                row.put("deptno",deptno);
                row.put("dname",dname);
                row.put("loc",loc);



            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        return  row;
    }
    public Dept selectById(int id){
        //
        Connection connection =  null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        Dept dept = null;
        try {
            connection = DBUtils.getConnection();


            String sql = "select deptno,dname,loc from dept where deptno = ?  ";
            stmt = connection.prepareStatement(sql);


            //可以绑定变量，没有？  可以不绑定
            stmt.setInt(1,id);

            //执行sql
            rs = stmt.executeQuery();
            if (rs.next()){
                int deptno = rs.getInt("deptno");
                String dname = rs.getString("dname");
                String loc = rs.getString("loc");

                //一行数据
                dept = new Dept();
                dept.setDeptno(deptno);
                dept.setDname(dname);
                dept.setLoc(loc);

            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        return  dept;
    }


    public int updateById(Dept dept){

        Connection connection =  null;
        PreparedStatement stmt = null;
        int count = 0 ;
        try {
            connection = DBUtils.getConnection();


            String sql = " UPDATE  `dept` SET   `dname` = ?, `loc` = ?  WHERE `deptno` = ?  ";
            stmt = connection.prepareStatement(sql);


            //绑定变量
            stmt.setString(1,dept.getDname() );
            stmt.setString(2,dept.getLoc() );
            stmt.setInt(3,dept.getDeptno() );


        //    执行sQL
            count = stmt.executeUpdate();


        } catch (SQLException e) {
            throw new RuntimeException(e);
        }


        return  count;
    }

    public int insert(Dept dept){

        Connection connection =  null;
        PreparedStatement stmt = null;
        int count = 0 ;
        try {
            connection = DBUtils.getConnection();


            String sql = " INSERT INTO `dept` (`deptno`, `dname`, `loc`) VALUES(?,?,?)  ";
            stmt = connection.prepareStatement(sql);


            //绑定变量
            int index = 0;

            stmt.setInt(++index,dept.getDeptno() );
            stmt.setString(++index,dept.getDname() );
            stmt.setString(++index,dept.getLoc() );


            //    执行sQL
            count = stmt.executeUpdate();


        } catch (SQLException e) {
            throw new RuntimeException(e);
        }


        return  count;
    }


    public int deleteById(int id){

        Connection connection =  null;
        PreparedStatement stmt = null;
        int count = 0 ;
        try {
            connection = DBUtils.getConnection();


            String sql = " delete from dept where deptno = ?";
            stmt = connection.prepareStatement(sql);


            //绑定变量
            stmt.setInt(1,id );

            //执行sQL
            count = stmt.executeUpdate();


        } catch (SQLException e) {
            throw new RuntimeException(e);
        }


        return  count;
    }

}
